CREATE TABLE transaccions (
    id         INTEGER      PRIMARY KEY AUTOINCREMENT
                            NOT NULL
                            UNIQUE,
    operacio   VARCHAR (24) DEFAULT NULL,
    import     FLOAT        DEFAULT '0',
    datahora   DATETIME     DEFAULT CURRENT_TIMESTAMP,
    token      VARCHAR (64) DEFAULT NULL,
    cautorit   VARCHAR (90) DEFAULT NULL,
	contracte  VARCHAR (90) DEFAULT NULL,
    autoritzat INTEGER (1)  DEFAULT NULL,
    codiauth   VARCHAR (90) DEFAULT NULL,
    resposta   VARCHAR (90) DEFAULT NULL
);
CREATE INDEX idx_transaccions_operacio ON transaccions (operacio);
CREATE INDEX idx_transaccions_token ON transaccions (token);

CREATE TABLE pagaments (
    id            INTEGER       PRIMARY KEY AUTOINCREMENT
                                NOT NULL
                                UNIQUE,
    transaccio_id [INTEGER KEY] NOT NULL
                                REFERENCES transaccions (id),
    operacio      VARCHAR (24),
    nom           VARCHAR (90),
    email         VARCHAR (90),
    import        FLOAT         DEFAULT '0',
    concepte      VARCHAR (200) DEFAULT NULL,
    status        VARCHAR (1)   DEFAULT 'T',
    token         VARCHAR (64),
    datahora      DATE          NOT NULL
                                DEFAULT (datetime('now', 'localtime') ),
    userdata      TEXT          DEFAULT NULL,
    callback      VARCHAR (90)  DEFAULT NULL
);
CREATE INDEX idx_pagaments_operacio ON pagaments (operacio);
CREATE INDEX idx_pagaments_token ON pagaments (token);

CREATE TABLE missatges (
    id            INTEGER       PRIMARY KEY AUTOINCREMENT
                                NOT NULL
                                UNIQUE,
    transaccio_id [INTEGER KEY] NOT NULL
                                REFERENCES transaccions (id) ON DELETE CASCADE,
    datahora      DATETIME,
    origen        VARCHAR (90),
    token         VARCHAR (64),
    missatge      TEXT
);

CREATE TABLE percobrar (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT
                            NOT NULL
                            UNIQUE,
    numfra    VARCHAR (90),
    nomdeutor VARCHAR (255),
    import    FLOAT         DEFAULT 0,
    concepte  VARCHAR (200) DEFAULT NULL,
    datahora  DATE          NOT NULL
                            DEFAULT (datetime('now', 'localtime') ),
    cautorit  VARCHAR (90),
    caconfig  VARCHAR (90),
    operacio  VARCHAR (20),
    status    VARCHAR (1) 
);

CREATE INDEX idx_percobrar_numfra ON percobrar (numfra);