JRumuC30

1CREATE TABLE IF NOT EXISTS `users` (
2  `id` INT NOT NULL AUTO_INCREMENT,
3  `username` VARCHAR(255) NOT NULL,
4  `email` VARCHAR(255) NOT NULL,
5  `password` VARCHAR(255) NOT NULL,
6  `active` TINYINT(1) NOT NULL DEFAULT 0,
7  `key` VARCHAR(255) NULL,
8  `key_type` TINYINT(2) NULL,
9  `key_expire` TIMESTAMP NULL,
10  `level` TINYINT(10) NULL DEFAULT 0,
11  `created` TIMESTAMP NOT NULL DEFAULT NOW(),
12  `banned` TINYINT(1) NOT NULL DEFAULT 0,
13  PRIMARY KEY (`id`),
14  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
15  UNIQUE INDEX `key_UNIQUE` (`key` ASC) )
16ENGINE = InnoDB;
17
18CREATE TABLE IF NOT EXISTS `trainers` (
19  `id` INT NOT NULL AUTO_INCREMENT,
20  `name` VARCHAR(255) NOT NULL,
21  `description` TEXT NOT NULL,
22  `instagram` VARCHAR(255) NULL,
23  `facebook` VARCHAR(255) NULL,
24  `website` VARCHAR(255) NULL,
25  `img` VARCHAR(255) NOT NULL,
26  `account` INT NULL,
27  PRIMARY KEY (`id`),
28  INDEX `fk_userid_idx` (`account` ASC) ,
29  CONSTRAINT `fk_userid`
30    FOREIGN KEY (`account`)
31    REFERENCES `users` (`id`)
32    ON DELETE NO ACTION
33    ON UPDATE NO ACTION)
34ENGINE = InnoDB;
35
36CREATE TABLE IF NOT EXISTS `plans` (
37  `id` INT NOT NULL AUTO_INCREMENT,
38  `title` VARCHAR(255) NOT NULL,
39  `description` TEXT NOT NULL,
40  `price` FLOAT UNSIGNED NOT NULL,
41  `promotion` FLOAT NULL,
42  `image` VARCHAR(255) NOT NULL,
43  `trainer` INT NULL,
44  `duration` INT UNSIGNED NOT NULL,
45  PRIMARY KEY (`id`),
46  INDEX `fk_trainerid_idx` (`trainer` ASC) ,
47  CONSTRAINT `fk_trainerid`
48    FOREIGN KEY (`trainer`)
49    REFERENCES `trainers` (`id`)
50    ON DELETE NO ACTION
51    ON UPDATE NO ACTION)
52ENGINE = InnoDB;
53
54CREATE TABLE IF NOT EXISTS `users_plans` (
55  `id` INT NOT NULL AUTO_INCREMENT,
56  `userid` INT NOT NULL,
57  `planid` INT NOT NULL,
58  `expire` DATETIME NOT NULL,
59  PRIMARY KEY (`id`),
60  INDEX `fk_user_idx` (`userid` ASC) ,
61  INDEX `fk_plan_idx` (`planid` ASC) ,
62  CONSTRAINT `fk_user`
63    FOREIGN KEY (`userid`)
64    REFERENCES `users` (`id`)
65    ON DELETE NO ACTION
66    ON UPDATE NO ACTION,
67  CONSTRAINT `fk_plan`
68    FOREIGN KEY (`planid`)
69    REFERENCES `plans` (`id`)
70    ON DELETE NO ACTION
71    ON UPDATE NO ACTION)
72ENGINE = InnoDB;
73
74CREATE TABLE IF NOT EXISTS `exercise` (
75  `id` INT NOT NULL,
76  `date` DATE NOT NULL,
77  `content` TEXT NOT NULL,
78  `info` TEXT NULL,
79  PRIMARY KEY (`id`))
80ENGINE = InnoDB;
81
82CREATE TABLE IF NOT EXISTS `exercises_to_plans` (
83  `id` INT NOT NULL AUTO_INCREMENT,
84  `planid` INT NOT NULL,
85  `exerciseis` INT NOT NULL,
86  PRIMARY KEY (`id`),
87  INDEX `fk_to_plan_idx` (`planid` ASC) ,
88  INDEX `fk_to_exercice_idx` (`exerciseis` ASC) ,
89  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
90  CONSTRAINT `fk_to_plan`
91    FOREIGN KEY (`planid`)
92    REFERENCES `plans` (`id`)
93    ON DELETE NO ACTION
94    ON UPDATE NO ACTION,
95  CONSTRAINT `fk_to_exercice`
96    FOREIGN KEY (`exerciseis`)
97    REFERENCES `exercise` (`id`)
98    ON DELETE NO ACTION
99    ON UPDATE NO ACTION)
100ENGINE = InnoDB;
101
102CREATE TABLE IF NOT EXISTS `transactions` (
103  `id` INT NOT NULL AUTO_INCREMENT,
104  `transactionId` VARCHAR(255) NOT NULL,
105  `userid` INT NOT NULL,
106  `value` FLOAT NOT NULL,
107  `plans` TEXT NOT NULL,
108  `created` TIMESTAMP NOT NULL DEFAULT NOW(),
109  PRIMARY KEY (`id`),
110  UNIQUE INDEX `transactionId_UNIQUE` (`transactionId` ASC) ,
111  INDEX `transactionId_INDEX` (`transactionId` ASC) )
112ENGINE = InnoDB;